package com.hzt.agprosaleplatform.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.hzt.agprosaleplatform.entity.*;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

public interface ExpertMapper extends BaseMapper<Expert> {

    @Select("SELECT e.uid, u.avatar as expert_avatar, e.name, e.position, e.belong, " +
           "e.province, e.city, e.descri, e.guidances, e.replies, e.status " +
           "FROM t_expert e " +
           "LEFT JOIN t_user u ON e.uid = u.uid " +
           "WHERE e.status = '1' " +  // 只选择审核通过的专家
           "ORDER BY RAND() " +       // 随机排序
           "LIMIT 3")                 // 限制返回3条记录
    List<Expert> selectRecommendedExperts();


    //小卡片展示所有问题
    @Select("SELECT q.qid, q.eid, q.status, q.user_ques as question, q.expert_ans as answer ," +
            "q.created_date, q.expert_date, q.adoptNumbers, " +
            "((SELECT COUNT(*) FROM t_more_answer ma WHERE ma.qid = q.qid) + " +
            "(CASE WHEN q.expert_ans IS NOT NULL THEN 1 ELSE 0 END)) as totalAnswer " +
            "FROM t_questions q")
    IPage<ExpertQuestion> ListAllQuestion(IPage<ExpertQuestion> page);


    //获取详情专家问答
    @Select("SELECT q.qid, " +
            "u.uid, " +
            "u.username as userName, " +
            "e.uid as eid, " +
            "e.name as expertName, " +
            "q.user_ques as question, " +
            "q.expert_ans as answer, " +
            "q.created_date, " +
            "q.expert_date, " +
            "q.status, " +
            "q.adoptNumbers " +
            "FROM t_questions q " +
            "LEFT JOIN t_user u ON q.uid = u.uid " +
            "LEFT JOIN t_expert e ON q.eid = e.uid WHERE q.qid = #{qid}")
    ExpertQuestion selectQuestionByQid(@Param("qid") int qid);

    @Update("UPDATE t_questions SET expert_ans = #{answer}, " +
    "expert_date = CURRENT_TIMESTAMP , status = 1 " +
    "WHERE qid = #{qid} ")
    int updateAnswer(@Param("qid") Integer qid,@Param("answer")String answer);

    @Insert("INSERT INTO t_more_answer (qid, uid, expert_ans, ans_date, adoptNumbers) " +
            "VALUES (#{qid}, #{uid}, #{answer}, CURRENT_TIMESTAMP, 0)")
    int moreAnswerToQ(@Param("uid") String uid,@Param("qid") Integer qid, @Param("answer") String answer);

    @Insert("INSERT INTO t_questions (uid, eid, user_ques, created_date, status, adoptNumbers) " +
            "VALUES (#{uid}, #{eid}, #{question}, CURRENT_TIMESTAMP, #{status}, #{adoptNumbers})")
    int insertQuestion(ExpertQuestion expertQuestion);

    @Select("SELECT * FROM t_expert WHERE uid = #{uid}")
    Expert selectExpertById(@Param("uid") String uid);

    //获取Eid下专家回复的帖子
    @Select("SELECT q.qid, q.user_ques, q.expert_ans, q.uid, q.expert_date, q.adoptNumbers " +
            "FROM t_questions q " +
            "WHERE q.uid = #{uid} AND q.status = 1 " +
            "ORDER BY q.expert_date DESC")
    List<ShortQuestionDTO> selectShortQuestionsByEid(@Param("uid") String uid);

    @Insert("INSERT INTO t_article_image (article_id, contentImg) VALUES (#{articleId}, #{imageUrl})")
    int insertArticleImage(@Param("articleId") Integer articleId, @Param("imageUrl") String imageUrl);

    @Select("SELECT contentImg FROM t_article_image WHERE article_id = #{articleId}")
    List<String> getArticleImages(@Param("articleId") Integer articleId);

    @Select("SELECT article_id,title,cover_image,view_count FROM t_article LIMIT 8")
    List<Article> getArticleCard();

    @Select("SELECT ma.more_ansId as id, " +
            "ma.qid, " +
            "ma.uid, " +
            "u.username as userName, " +
            "e.name as expertName, " +
            "ma.expert_ans as answer, " +
            "ma.ans_date as expert_date, " +
            "ma.adoptNumbers " +
            "FROM t_more_answer ma " +
            "LEFT JOIN t_user u ON ma.uid = u.uid " +
            "LEFT JOIN t_expert e ON ma.uid = e.uid " +
            "WHERE ma.qid = #{qid} " +
            "ORDER BY ma.ans_date DESC")
    List<ExpertQuestion> getMoreAnswerByQid(@Param("qid") Integer qid);
}
